[演算法][SQL]演算法挑戰系列(11)-Trips and Users

sql ms sql

神Q超人 2018-07-13 23:06:02 ‧ 3951 瀏覽

分享至

嗨啊！大家好，又到一個禮拜一次的這個時候了，剛剛我在選題目的時候，突然發現一件事，那就是我們SQL的題目不知不覺快要被破台了XD，所以以後SQL的題目可能會變少，要好好珍惜最後幾題，哈哈哈，不過其實不是因為那個網站的題目少，而是因為大部分的題目都被鎖起來了，他會在每個禮拜持續增加，說不定之後還會有機會分享，那接著就開始這禮拜的題目吧！

題目：Trips and Users
難易度：高
題目內容：沒錯，這一次有個Trips表，內有計程車的出租資料，裡面包含唯一值的Id和客戶編號Client_Id、出租車的司機編號Driver_Id還有出租狀態Status和出租日期Request_at，而出租狀態Status有分完成completed和司機取消cancelled_by_driver及客戶取消cancelled_by_client三種。

另外的Users人員資料表記錄著包括客戶及司機，所有使用者的資料，而他有個Banned欄位記錄著是否為受限用戶，YES為受限，NO為正常使用。

好的，那說了那麼多，這一次我們要做的事情就是，要找出出租日期2013-10-01到2013-10-03間未受限使用者中每一天的取消出租比例，並四捨五入到小數點第二位，一句話把題目簡單講完，但我有標記重點XD，可以看看以下例子：

例如：
Trips表內容：

Id	Client_Id	Driver_Id	City_Id	Status	Request_at
1	1	10	1	completed	2013-10-01
2	2	11	1	cancelled_by_driver	2013-10-01
3	3	12	6	completed	2013-10-01
4	4	13	6	cancelled_by_client	2013-10-01
5	1	10	1	completed	2013-10-02
6	2	11	6	completed	2013-10-02
7	3	12	6	completed	2013-10-02
8	2	12	12	completed	2013-10-03
9	3	10	12	completed	2013-10-03
10	4	13	12	cancelled_by_driver	2013-10-03
`Users`表內容：
Users_Id	Banned	Role
-------------	-------------	-------------
1	No	client
2	Yes	client
3	No	client
4	No	client
10	No	driver
11	No	driver
12	No	driver
13	No	driver
查詢結果：

 Day    | Cancellation Rate

------------- | -------------
2013-10-01 | 0.33
2013-10-02 | 0.00
2013-10-03 | 0.50
Cancellation Rate欄位內的資料就是個日期的未受限用戶取消比例，例如2013-10-01這個日期中有四筆資料，但是Client_Id為2，他是受限用戶，不列入計算，所以2013-10-01剩下三筆出租資料，而三筆資料中又有一筆資料取消，所以取消的比例為1/3四捨五入到小數點第二位為0.33，其他兩天以此類推。

這一題解得還滿順的，以下解法：

/*(1)這邊查詢日期欄位資料*/
SELECT t.Request_at Day, 
/*(8)因為轉成FLOAT的關係，所以用ROUND把小數位數控制在兩位*/
ROUND(
    /*(6)這一層的SUM把內層的資料加總起來，得到取消資料的數量*/
    SUM(
        /*(5)內層先處理，用CASE WHEN判斷把取消的資料變為1，其餘為0*/
        CASE WHEN t.Status = 'cancelled_by_driver' OR t.Status = 'cancelled_by_client' THEN 1 ELSE 0 END
        )
    /*(7)這邊除上用COUNT取得該日期的所有資料數目，
    我把總資料數轉成FOLAT型態，這樣他除起來就會有小數點*/
    /CONVERT(FLOAT,COUNT(*)) 
,2) [Cancellation Rate]

FROM Trips t 
/*(3)只撈出不等於受限資料的使用者*/
WHERE t.Client_Id  NOT IN (SELECT Users_Id FROM Users WHERE Banned = 'Yes') 
/*(4)這邊指定日期，因為題目只要這三天的資料*/
AND t.Request_at BETWEEN '2013-10-01' AND '2013-10-03'
/*(2)用GROUP BY讓日期可以把每天的資料作為一個群組，讓我們去計算*/
GROUP BY t.Request_at

然後這次的成績是...我就不貼了XD，哈哈哈，其實不是因為太爛啦，只是我覺得這個出現的區間太大了，這禮拜我1%~96%都跑到過，哈哈哈，個人是覺得和網路速度應該有關係，所以就當參考用就好！

啊啊，其實看了文章感覺這一題的解法大家都差不多，只有些是另外JOIN子查詢計算資料，而我是直接在SELECT寫完，而這次有沒有大大用一樣的方式寫呢？有！而且就在第一則！！不過是MySQL版本的，貼出來給大家看看XD，因為只是換個語言而已，所以我只註解標記的地方，哈哈哈！
原解答網址

SELECT Request_at as Day,
    ROUND(
        COUNT(
            /*因為MySQL沒有CASE WHEN取而代之用IF判斷，如果不等於就是TRUE(1)，不然就NULL(0)*/
            IF(Status != 'completed', TRUE, NULL)) 
        / COUNT(*)
    , 2) AS 'Cancellation Rate'
FROM Trips
WHERE (Request_at BETWEEN '2013-10-01' AND '2013-10-03')
      AND Client_id NOT IN (SELECT Users_Id FROM Users WHERE Banned = 'Yes')
GROUP BY Request_at;

對耶！在幫忙下註解的時候赫然發現，我就直接判斷completed就好了，我還去判斷兩種狀態，真的是一心只想著取消，都忘記換個方向思考了XD，不過我就不改我的版本了，看版上大大有沒有更棒的解法，哈哈哈！

這次我也提供了現在風靡板上的MSSQL連結讓各位大大去玩XD。

話說文章的最後大家有沒有發現這禮拜的難度居然是高級的，哈哈哈，我一直都沒提想說這樣解起來會不會比較簡單，才不會有慘了，這次一定很難，的心理作用XD，啊選擇困難的原因只是單純想快點全破而已，哈哈哈，如果有一起做的大大，現在的進度應該也和我一樣，快解完SQL的題目了，像下面這張圖一樣：

搞定這題後，大家一起破台SQL的困難級題目
如果以上文章中有任何不懂的，或是解釋錯誤，都麻煩各位大大留言告訴我，我會盡快改進，謝謝大家

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 則留言

暐翰

iT邦大師 1 級 ‧ 2018-07-14 12:30:54

又到大大每禮拜一挑戰

我使用sql-server來做題目
一開始解法跟大大都差不多

後面就想說玩點特別的使用distinct + sum over
相對效能一定會比較差XDD

--把Banned的使用者篩選掉
select distinct [Request_at] [Day]
	--只需要取到小數點二位
	,round(
		--算取消率，特別使用sum少一次轉型
		sum(case when [Status]<>'completed' then 1.00 else 0.00 end)
			over (partition by [Request_at])
		/count(1) over (partition by [Request_at])
	,2) [Cancellation_Rate]
from Trips
where 1=1
	--篩選 只要10/01 - 03的資料 
	and [Request_at] between '2013-10-01' and '2013-10-03'
	--把Banned的使用者篩選掉
	and Client_Id not in (
	  select Users_Id from Users
	  where Banned = 'Yes'
	) 
;

結果:

Day	Cancellation_Rate
2013-10-01	0.330000
2013-10-02	0.000000
2013-10-03	0.500000

線上測試連結

回應 2
檢舉

神Q超人 iT邦研究生 5 級 ‧ 2018-07-14 14:00:03 檢舉

哈哈，這樣子就不用GROUP BY了！
而且大大直接在CASE WHEN中指定小數點1.00，這我也沒想到XD
這樣就像註解說的，可以少一次轉型！

我想這些SQL的問題已經難不倒大大們了，
以後可能要比誰寫出最有趣的寫法

小碼農米爾 iT邦高手 1 級 ‧ 2018-07-14 16:16:45 檢舉

沒想到判斷 completed 和直接轉型 1.00 +1

登入發表回應

小碼農米爾

iT邦高手 1 級 ‧ 2018-07-14 16:08:16

MS SQL 連結

SELECT
	CONVERT(NVARCHAR(10), Request_at, 120)AS [Day],
	ROUND(
		SUM(CASE WHEN [Status]='cancelled_by_client' OR 
                      [Status]='cancelled_by_driver' THEN 1 ELSE 0 END) / 
		CAST(COUNT(Id) AS float), 2
	) AS [Cancellation Rate]
FROM @Trips AS A
LEFT JOIN @Users AS B ON B.Users_Id=A.Client_Id
WHERE Request_at >= '2013-10-01' AND Request_at <= '2013-10-03' AND 
      Banned = 'No' 
GROUP BY Request_at